import os
from IPython.display import display, HTML
ModuleFolder='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\M01\\'
os.chdir(ModuleFolder)
for file in os.listdir(ModuleFolder):
#if file.lower().endswith('HTMLs.html'):
#continue
if file.lower().endswith('.html'):
link=file
#print(link)
display(HTML(ModuleFolder+link))
elif file.lower().endswith('.htm'):
link=file
#print(link)
display(HTML(ModuleFolder+link))
else:
continue
SELECT statement examples
Estimated time: 5 min
Objectives
At the end of this reading, you will learn how to:
- Use various SELECT queries to retrieve data from the database.
SELECT statement usage
SELECT is classified as a Database Query command used to retrieve information from a database table.
There are various forms in which a SELECT statement is used.
- The general syntax of a SELECT statement retrieves the data under the listed columns from Table_1. The code is:
- 1
SELECT COLUMN1, COLUMN2, ... FROM TABLE_1 ;
- To retrieve all columns from a table, use " * " instead of specifying individual column names. The code below retrieves the entire table.
- 1
SELECT * FROM TABLE_1 ;
- Use the WHERE clause to filter the required data based on a predicate. The code below filters the response to only the entries that match the predicate.
- 1
SELECT <COLUMNS> FROM TABLE_1 WHERE <predicate> ;
SELECT examples
Let's look at these codes in action. Below is a database table called 'COUNTRY,' which contains the columns ID, Name, and CCode. Here, CCode is a 2 letter country code.
| ID | Name | CCode |
|---|---|---|
| 1 | United States of America | US |
| 2 | China | CH |
| 3 | Japan | JA |
| 4 | Germany | GE |
| 5 | India | IN |
| 6 | United Kingdom | UK |
| 7 | France | FR |
| 8 | Italy | IT |
| 9 | Canada | CA |
| 10 | Brazil | BR |
Example #1
When we apply the SELECT code SELECT * FROM COUNTRY ;, the query retrieves all rows and columns from the database table named COUNTRY.
- 'SELECT *' instructs the database to select all columns from the table.
- 'FROM COUNTRY' specifies the table from which to retrieve the data. In this case, it's the "COUNTRY" table, so the entire table appears, as shown below.
Response:
| ID | Name | CCode |
|---|---|---|
| 1 | United States of America | US |
| 2 | China | CH |
| 3 | Japan | JA |
| 4 | Germany | GE |
| 5 | India | IN |
| 6 | United Kingdom | UK |
| 7 | France | FR |
| 8 | Italy | IT |
| 9 | Canada | CA |
| 10 | Brazil | BR |
Example #2
The SQL query SELECT ID, Name FROM COUNTRY ; retrieves specific columns from a database table named 'COUNTRY'.
- 'SELECT ID, Name' instructs the database to select two specific columns from the table: "ID" and "Name." It will return these two columns for each row that matches the query criteria.
- 'FROM COUNTRY' specifies the table from which to retrieve the data, which is the "COUNTRY" table. The table below shows that only the "ID" and "CCode" columns were retrieved.
Response:
| ID | Name |
|---|---|
| 1 | United States of America |
| 2 | China |
| 3 | Japan |
| 4 | Germany |
| 5 | India |
| 6 | United Kingdom |
| 7 | France |
| 8 | Italy |
| 9 | Canada |
| 10 | Brazil |
Example #3
The SQL query SELECT * FROM COUNTRY WHERE ID <= 5 ; retrieves all columns from the "COUNTRY" table where the value in the "ID" column is less than or equal to 5.
- `SELECT * instructs the database to select all columns from the specified table.
- FROM COUNTRY specifies the table from which to retrieve the data, which is the 'COUNTRY' table.
- WHERE ID <= 5 ; is a condition that filters the rows from the table. It will only return rows where the value in the "ID" column is less than or equal to 5. In the table below, you can see that only rows 1-5 were retrieved.
Response:
| ID | Name | CCode |
|---|---|---|
| 1 | United States of America | US |
| 2 | China | CH |
| 3 | Japan | JA |
| 4 | Germany | GE |
| 5 | India | IN |
Example #4
The SQL querySELECT * FROM COUNTRY WHERE CCode = 'CA' ; retrieves all columns from the "COUNTRY" table where the value in the "CCode" column is equal to 'CA'.
- `SELECT * instructs the database to select all columns from the specified table.
- FROM COUNTRY specifies the bale from which to retrieve the data, which is the 'COUNTRY' table.
- WHERE CCode = 'CA'; is a condition that filters the rows from the table. It will only return rows where the value in the "CCode" column is equal to 'CA.' In the table below, you will find that only the CA column was retrieved.
Response:
| ID | Name | CCode |
|---|---|---|
| 9 | Canada | CA |
In the lab that follows later in the module, you will apply these concepts and practice more SELECT queries hands-on.
In this reading, you learned that:
- SELECT is a Database Query command that retrieves information from a database table.
- The SELECT statement has various forms depending on what action you require.
- The general syntax will retrieve the data under the listed columns from a named table.
- Use "*" to retrieve all columns from a table without specifying individual column names.
- Use the WHERE clause to filter the data based on a predicate.
Author(s)
Rav Ahuja
Abhishek Gagneja
SQL Cheat Sheet: Basics
| Command | Syntax | Description | Example |
|---|---|---|---|
| SELECT |
SELECT column1, column2, ... FROM table_name;
|
SELECT statement is used to fetch data from a database.
|
SELECT city FROM placeofinterest;
|
| WHERE |
SELECT column1, column2, ...FROM table_name WHERE condition;
|
WHERE clause is used to extract only those records that fulfill a specified condition.
|
SELECT * FROM placeofinterest WHERE city = 'Rome' ;
|
| COUNT |
SELECT COUNT * FROM table_name ;
|
COUNT is a function that takes the name of a column as argument and counts the number of rows when the column is not NULL.
|
SELECT COUNT(country) FROM placeofinterest WHERE country='Canada';
|
| DISTINCT |
SELECT DISTINCT columnname FROM table_name;
|
DISTINCT function is used to specify that the statement is a query which returns unique values in specified columns.
|
SELECT DISTINCT country FROM placeofinterest WHERE type='historical';
|
| LIMIT |
SELECT * FROM table_name LIMIT number;
|
LIMIT is a clause to specify the maximum number of rows the result set must have.
|
SELECT * FROM placeofinterest WHERE airport="pearson" LIMIT 5;
|
| INSERT |
INSERT INTO table_name (column1,column2,column3...) VALUES(value1,value2,value3...);
|
INSERT is used to insert new rows in the table.
|
INSERT INTO placeofinterest (name,type,city,country,airport) VALUES('Niagara Waterfalls','Nature','Toronto','Canada','Pearson');
|
| UPDATE |
UPDATE table_name SET[[column1]=[VALUES]] WHERE [condition];
|
UPDATE used to update the rows in the table.
|
UPDATE placeofinterest SET name = 'Niagara Falls' WHERE name = "Niagara Waterfalls";
|
| DELETE |
DELETE FROM table_name WHERE [condition];
|
DELETE statement is used to remove rows from the table which are specified in the WHERE condition.
|
DELETE FROM placeofinterest WHERE city IN ('Rome','Vienna');
|
Author(s)

Reading: Examples to ALTER and TRUNCATE tables using MySQL
Estimated time to complete: 5 minutes
In the previous video, the ALTER and TRUNCATE syntax applies to DB2. There are variations in syntax between different databases. This reading will explore some examples of ALTER and TRUNCATE statements using MySQL.
Objective(s)
At the end of this reading, you will be able to:
- Use the ALTER TABLE statement in the correct syntax.
- Use TRUNCATE statements in syntax.
- Execute examples of ALTER and TRUNCATE statements.
ALTER TABLE
ALTER TABLE statements can be used to add or remove columns from a table, to modify the data type of columns, to add or remove keys, and to add or remove constraints. The syntax of the ALTER TABLE statement is:
ADD COLUMN syntax
- 1
- 2
ALTER TABLE table_nameADD column_name data_type;
A variation of the syntax for adding column is:
- 1
- 2
ALTER TABLE table_nameADD COLUMN column_name data_type;
By default, all the entries are initially assigned the value NULL. You can then use UPDATE statements to add the necessary column values.
For example, to add a telephone_number column to the author table in the library database, the statement will be written as:
- 1
- 2
ALTER TABLE authorADD telephone_number BIGINT;
Here, BIGINT is a data type for Big Integer.
After adding the entries to the new column, a sample output is shown below.

Modify column data type
- 1
- 2
ALTER TABLE table_nameMODIFY column_name data_type;
Sometimes, the data presented may be in a different format than required. In such a case, we need to modify the data_type of the column. For example, using a numeric data type for telephone_number means you cannot include parentheses, plus signs, or dashes as part of the number. For such entries, the appropriate choice of data_type is CHAR.
To modify the data type, the statement will be written as:
- 1
- 2
ALTER TABLE authorMODIFY telephone_number CHAR(20);
The entries can then be updated using UPDATE statements. An updated version of the "author" table is shown below.

TRUNCATE Table
TRUNCATE TABLE statements are used to delete all of the rows in a table. The syntax of the statement is:
- 1
TRUNCATE TABLE table_name;
So, to truncate the "author" table, the statement will be written as:
- 1
TRUNCATE TABLE author;
The output would be as shown in the image below.

Note: The TRUNCATE statement will delete the rows and not the table.
Author
Additional Contributor(s)
Reading: Examples to CREATE and DROP tables
Objective(s)
At the end of this lab, you will be able to:
- Create and Drop tables in the database.
Estimated time to complete: 5 minutes
CREATE TABLE statement
In the previous video, we saw the general syntax to create a table:
- 1
- 2
- 3
- 4
- 5
- 6
CREATE TABLE TableName (COLUMN1 datatype,COLUMN2 datatype,COLUMN3 datatype,...);
Consider the following examples:
- Create a TEST table with two columns - ID of type integer and NAME of type varchar. For this, we use the following SQL statement.
- 1
- 2
- 3
- 4
CREATE TABLE TEST (ID int,NAME varchar(30));
- Create a COUNTRY table with an integer ID column, a two-letter country code column, and a variable length country name column. For this, we may use the following SQL statement.
- 1
- 2
- 3
- 4
- 5
CREATE TABLE COUNTRY (ID int,CCODE char(2),Name varchar(60));
- In the example above, make ID a primary key. Then, the statement will be modified as shown below.
- 1
- 2
- 3
- 4
- 5
- 6
CREATE TABLE COUNTRY (ID int NOT NULL,CCODE char(2),Name varchar(60)PRIMARY KEY (ID));
In the above example, the ID column has the NOT NULL constraint added after the datatype, meaning that it cannot contain a NULL or an empty value. This is added since the database does not allow Primary Keys to have NULL values.
DROP TABLE
If the table you are trying to create already exists in the database, you will get an error indicating table XXX.YYY already exists. To circumvent this error, create a table with a different name or first DROP the existing table. It is common to issue a DROP before doing a CREATE in test and development scenarios.
The syntax to drop a table is:
- 1
DROP TABLE TableName;
For example, consider that you wish to drop the contents of the table COUNTRY if a table exists in the dataset with the same name. In such a case, the code for the last example becomes
- 1
- 2
- 3
- 4
- 5
- 6
- 7
DROP TABLE COUNTRY;CREATE TABLE COUNTRY (ID int NOT NULL,CCODE char(2),Name varchar(60)PRIMARY KEY (ID));
WARNING: Before dropping a table, ensure it doesn't contain important data that can't be recovered easily.
Note that if the table does not exist and you try to drop it, you will see an error like XXX.YYY is an undefined name. You can ignore this error if the subsequent CREATE statement is executed successfully.
In a hands-on lab later in this module, you will practice creating tables and other SQL statements.
Author(s)
Rav Ahuja
Additional Contributor
SQL Scripts - Uses and Applications
SQL Scripts
SQL scripts are a series of commands or a program that will be executed on an SQL server.
SQL scripts are useful for making complex database changes and can be used to create, modify, or delete database objects such as tables, views, stored procedures, and functions.
Applications of SQL Scripts
Here are some of the things that you can do with SQL scripts:
Create tables
You can use SQL scripts to create new tables in your database. This is useful when you need to add new functionality to your application or when you want to store new types of data.Drop tables
SQL scripts often have commands to Drop tables from databases. This is especially important before Create table commands to make sure that a table with the same name doesnt exist in the database already.Insert data
SQL scripts can also be used to insert data into your tables. This is useful when you need to populate your database with test data or when you want to import data from an external source.Update data
You can use SQL scripts to update existing data in your tables. This is useful when you need to correct errors or update records based on changing business requirements.Delete data
SQL scripts can also be used to delete data from your tables. This is useful when you need to remove old or obsolete records from your database.Create views
Views are virtual tables that allow you to query data from multiple tables as if they were a single table. You can use SQL scripts to create views that simplify complex queries and make it easier to work with your data.Create stored procedures
Stored procedures are precompiled SQL statements that can be executed on demand. You can use SQL scripts to create stored procedures that encapsulate complex business logic and make it easier to manage your database.Create triggers
Triggers are special types of stored procedures that are automatically executed in response to certain events, such as an insert, update, or delete operation. You can use SQL scripts to create triggers that enforce business rules and maintain data integrity.
Example: Creating Tables
Let us execute a script containing the CREATE TABLE commands for all the tables in a given dataset, rather than create each table manually by typing the DDL commands in the SQL editor.
Note the following points about these scripts.
- SQL scripts are basically a set of SQL commands compiled in a single file.
- Each command must be terminated with a delimiter or terminator. Most often, the default delimiter is a semicolon
;. - It is advisable to keep the extension of the file as
.sql. - Upon importing this file in the phpMyAdmin interface, the commands in the file are run sequentially.
Consider the following script
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
DROP TABLE IF EXISTS PATIENTS;DROP TABLE IF EXISTS MEDICAL_HISTORY;DROP TABLE IF EXISTS MEDICAL_PROCEDURES;DROP TABLE IF EXISTS MEDICAL_DEPARTMENTS;DROP TABLE IF EXISTS MEDICAL_LOCATIONS;CREATE TABLE PATIENTS (PATIENT_ID CHAR(9) NOT NULL,FIRST_NAME VARCHAR(15) NOT NULL,LAST_NAME VARCHAR(15) NOT NULL,SSN CHAR(9),BIRTH_DATE DATE,SEX CHAR,ADDRESS VARCHAR(30),DEPT_ID CHAR(9) NOT NULL,PRIMARY KEY (PATIENT_ID));CREATE TABLE MEDICAL_HISTORY (MEDICAL_HISTORY_ID CHAR(9) NOT NULL,PATIENT_ID CHAR(9) NOT NULL,DIAGNOSIS_DATE DATE,DIAGNOSIS_CODE VARCHAR(10),MEDICAL_CONDITION VARCHAR(100),DEPT_ID CHAR(9),PRIMARY KEY (MEDICAL_HISTORY_ID));CREATE TABLE MEDICAL_PROCEDURES (PROCEDURE_ID CHAR(9) NOT NULL,PROCEDURE_NAME VARCHAR(30),PROCEDURE_DATE DATE,PATIENT_ID CHAR(9) NOT NULL,DEPT_ID CHAR(9),PRIMARY KEY (PROCEDURE_ID));CREATE TABLE MEDICAL_DEPARTMENTS (DEPT_ID CHAR(9) NOT NULL,DEPT_NAME VARCHAR(15),MANAGER_ID CHAR(9),LOCATION_ID CHAR(9),PRIMARY KEY (DEPT_ID));CREATE TABLE MEDICAL_LOCATIONS (LOCATION_ID CHAR(9) NOT NULL,DEPT_ID CHAR(9) NOT NULL,LOCATION_NAME VARCHAR(50),PRIMARY KEY (LOCATION_ID, DEPT_ID));
This script incorporates commands to first drop any tables with the mentioned names in the database. After that, the script contains commands to create 5 different tables. All these commands are executed sequentially on the interface.
The contents of this file can be saved in a .sql
file format and executed on the phpMyAdmin interface. This can be done
by first selecting the database, uploading the SQL script in the
provided space, and executing it, as shown in the image below.
Upon successful execution of each statement in sequence, an note appears on the interface as shown in the image below. It is also prudent to note that the tables created are now visible in the tree structure on the left under the selected database.

You may click any of the tables to see its Table Definition (its list of columns, data types, and so on). The image below displays the structure of the table PATIENTS.

Author(s)
SQL Cheat Sheet: CREATE TABLE, ALTER, DROP, TRUNCATE
| Command | Syntax | Description | Example |
|---|---|---|---|
| CREATE TABLE |
MySQL/DB2: CREATE TABLE table_name (col1 datatype optional keyword, col2 datatype optional keyword,col3 datatype optional keyword,..., coln datatype optional keyword)
|
CREATE TABLE statement is to create the table. Each column
in the table is specified with its name, data type and an optional
keyword which could be PRIMARY KEY, NOT NULL, etc.,
|
MySQL/DB2:
CREATE TABLE employee ( employee_id char(2) PRIMARY KEY, first_name varchar(30) NOT NULL, mobile int);
|
| ALTER TABLE - ADD COLUMN |
MySQL/DB2:
|
ALTER TABLE statement is used to add the columns to a table.
|
MySQL/DB2
|
| ALTER TABLE - ALTER COLUMN |
MySQL: ALTER TABLE table_name MODIFY column_name_1 new_data_type;
DB2: |
MySQL: ALTER TABLE MODIFY MODIFY clause is used with the ALTER TABLE statement to modify the data type of columns.
Db2: |
MySQL: ALTER TABLE employee MODIFY mobile CHAR(20);
DB2: |
| ALTER TABLE - DROP COLUMN |
MySQL/DB2: ALTER TABLE table_name DROP COLUMN column_name_1 ;
|
ALTER TABLE DROP COLUMN statement is used to remove columns from a table.
|
MySQL/DB2:
|
| ALTER TABLE - RENAME COLUMN |
MySQL:ALTER TABLE table_name CHANGE COLUMN current_column_name new_column_name datatype [optional keywords];
DB2: |
MySQL: ALTER TABLE CHANGE COLUMN CHANGE COLUMN clause is used to rename the columns in a table.
DB2: |
MySQL: ALTER TABLE employee CHANGE COLUMN first_name name VARCHAR(255);
DB2: |
| TRUNCATE TABLE |
MySQL: TRUNCATE TABLE table_name;
DB2: |
MySQL: TRUNCATE TABLE statement is used to delete all of the rows in a table.
Db2: The |
MySQL: TRUNCATE TABLE employee;
DB2: |
| DROP TABLE | MySQL/DB2DROP TABLE table_name ;
|
Use the DROP TABLE statement to delete a table from a
database. If you delete a table that contains data, by default the data
will be deleted alongside the table.
|
MySQL/DB2:
|
Author(s)
Himanshu Birla
Niveditha Pandith TS

SQL Cheat Sheet: Intermediate - LIKE, ORDER BY, GROUP BY
| Command | Syntax (MySQL/DB2) | Description | Example (MySQL/DB2) |
|---|---|---|---|
| LIKE |
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
|
LIKE operator is used in a WHERE clause to search for a specified pattern in a column.Two wildcards often used in conjunction with the LIKE operator are percent sign(%) and underscore sign (_), depending upon the SQL engine being used. |
SELECT f_name , l_name
FROM employees
WHERE address LIKE '%Elgin,IL%';
This command will output all entries with Elgin,IL in the Address.
|
| BETWEEN |
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
|
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
|
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
This generates all records of employees with salaries between 40000 and 80000. |
| ORDER BY |
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
|
ORDER BY keyword is used to sort the result-set in
ascending or descending order. The default is ascending. In case of
multiple columns in ORDER BY, the sorting will be done in the sequence
of the appearance of the arguments.
|
SELECT f_name, l_name, dep_id
FROM employees
ORDER BY dep_id DESC, l_name;
This displays the first name, last name, and department ID of employees, first sorted in descending order of department IDs and then sorted alphabetically as per their last names. |
| GROUP BY |
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)
|
GROUP BY clause is used in collaboration with the SELECT statement to arrange data with identical values into groups.
|
SELECT dep_id, COUNT(*)
FROM employees
GROUP BY dep_id;
This returns the department IDs and the number of employees in them, grouped by the department IDs. |
| HAVING |
SELECT column_name(s)
FROM table_name
GROUP BY column_name(s)
HAVING condition
|
HAVING clause is used in conjunction with GROUP BY
clause in collaboration with the SELECT statement in order to filter
the data as per the given condition and then group as per identical
values of a specified parameter.
|
SELECT DEP_ID, COUNT(*) AS "NUM_EMPLOYEES", AVG(SALARY) AS "AVG_SALARY"
FROM EMPLOYEES
GROUP BY DEP_ID
HAVING count(*) < 4
ORDER BY AVG_SALARY;
|
Author(s)
SQL Cheat Sheet: FUNCTIONS and Implicit JOIN
| Command | Syntax (MySQL/DB2) | Description | Example (MySQL/DB2) |
|---|---|---|---|
| COUNT |
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
|
COUNT function returns the number of rows that match a specified criterion.
|
SELECT COUNT(dep_id) FROM employees;
|
| AVG |
SELECT AVG(column_name)
FROM table_name
WHERE condition;
|
AVG function returns the average value of a numeric column.
|
SELECT AVG(salary) FROM employees;
|
| SUM |
SELECT SUM(column_name)
FROM table_name
WHERE condition;
|
SUM function returns the total sum of a numeric column.
|
SELECT SUM(salary) FROM employees;
|
| MIN |
SELECT MIN(column_name)
FROM table_name
WHERE condition;
|
MIN function returns the smallest value of the SELECTED column.
|
SELECT MIN(salary) FROM employees;
|
| MAX |
SELECT MAX(column_name)
FROM table_name
WHERE condition;
|
MAX function returns the largest value of the SELECTED column.
|
SELECT MAX(salary) FROM employees;
|
| ROUND |
SELECT ROUND(2number, decimals, operation) AS RoundValue;
|
ROUND function rounds a number to a specified number of decimal places.
|
SELECT ROUND(salary) FROM employees;
|
| LENGTH |
SELECT LENGTH(column_name)
FROM table;
|
LENGTH function returns the length of a string (in bytes).
|
SELECT LENGTH(f_name) FROM employees;
|
| UCASE |
SELECT UCASE(column_name) FROM table;
|
UCASE function displays the column name in each table in uppercase.
|
SELECT UCASE(f_name) FROM employees;
|
| LCASE |
SELECT LCASE(column_name) FROM table;
|
LCASE function displays the column name in each table in lowercase.
|
SELECT LCASE(f_name) FROM employees;
|
| DISTINCT |
SELECT DISTINCT column_name FROM table;
|
DISTINCT function is used to display data without duplicates.
|
SELECT DISTINCT UCASE(f_name) FROM employees;
|
| DAY |
SELECT DAY(column_name) FROM table
|
DAY function returns the day of the month for a given date.
|
SELECT DAY(b_date) FROM employees where emp_id = 'E1002';
|
| CURRENT_DATE |
SELECT CURRENT_DATE;
|
CURRENT_DATE is used to display the current date.
|
SELECT CURRENT_DATE;
|
| DATEDIFF() |
SELECT DATEDIFF(date1, date2);
|
DATEDIFF() is used to calculate the difference between two
dates or time stamps. The default value generated is the difference in
number of days.
|
SELECT DATEDIFF(CURRENT_DATE, date_column) FROM table;
|
| FROM_DAYS() |
SELECT FROM_DAYS(number_of_days);
|
FROM_DAYS() is used to convert a given number of days to YYYY-MM-DD format.
|
SELECT FROM_DAYS(DATEDIFF(CURRENT_DATE, date_column)) FROM table;
|
| DATE_ADD() |
SELECT DATE_ADD(date, INTERVAL n type);
|
DATE_ADD() is used to calculate the date after lapse of
mentioned number of units of date type, i.e. if n=3 and type=DAY, the
result is a date 3 days after what is mentioned in date column. The type
valiable can also be months or years.
|
SELECT DATE_ADD(date, INTERVAL 3 DAY);;
|
| DATE_SUB() |
SELECT DATE_SUB(date, INTERVAL n type);
|
DATE_SUB() is used to calculate the date prior to the
record date by mentioned number of units of date type, i.e. if n=3 and
type=DAY, the result is a date 3 days before what is mentioned in date
column. The type valiable can also be months or years.
|
SELECT DATE_SUB(date, INTERVAL 3 DAY);;
|
| Subquery |
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
|
Subquery is a query within another SQL query and embedded within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. |
|
| Implicit Inner Join |
SELECT column_name(s)
FROM table1, table2
WHERE table1.column_name = table2.column_name;
|
Implicit Inner Join combines two or more records but displays only matching values in both tables.
Inner join applies only the specified columns.
|
SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;
|
| Implicit Cross Join |
SELECT column_name(s)
FROM table1, table2;
|
Implicit Cross Join is defined as a Cartesian product where
the number of rows in the first table is multiplied by the number of
rows in the second table.
|
SELECT * FROM employees, jobs;
|
Author(s)
SQL Cheat Sheet: Accessing Databases using Python
SQLite
| Topic | Syntax | Description | Example |
|---|---|---|---|
| connect() |
sqlite3.connect()
|
Create a new database and open a database connection to allow sqlite3 to work with it. Call sqlite3.connect()
to create a connection to the database INSTRUCTOR.db in the current
working directory, implicitly creating it if it does not exist.
|
|
| cursor() |
con.cursor()
|
To execute SQL statements and fetch results from SQL queries, use a database cursor. Call con.cursor() to create the Cursor.
|
|
| execute() |
cursor_obj.execute()
|
The execute method in Python's SQLite library allows to
perform SQL commands, including retrieving data from a table using a
query like "Select * from table_name." When you execute this command,
the result is obtained as a collection of table data stored in an
object, typically in the form of a list of lists.
|
|
| fetchall() |
cursor_obj.fetchall()
|
The |
|
| fetchmany() |
cursor_obj.fetchmany()
|
The |
|
| read_sql_query() |
read_sql_query()
|
|
|
| shape |
dataframe.shape
|
It provides a tuple indicating the shape of a DataFrame or Series, represented as (number of rows, number of columns). |
|
| close() |
con.close()
|
con.close() is a method used to close the connection to a
MySQL database. When called, it terminates the connection, releasing
any associated resources and ensuring the connection is no longer
active. This is important for managing database connections efficiently
and preventing resource leaks in your MySQL database interactions.
|
|
| CREATE TABLE |
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
|
The CREATE TABLE statement is used to define and create a
new table within a database. It specifies the table's name, the
structure of its columns (including data types and constraints), and any
additional properties such as indexes. This statement essentially sets
up the blueprint for organizing and storing data in a structured format
within the database.
|
|
| barplot() |
seaborn.barplot(x="x-axis_variable", y="y-axis_variable", data=data)
|
seaborn.barplot() is a function in the Seaborn Python
data visualization library used to create a bar plot, also known as a
bar chart. It is particularly used to display the relationship between a
categorical variable and a numeric variable by showing the average
value for each category.
|
|
| read_csv() |
df = pd.read_csv('file_path.csv')
|
read_csv() is a function in Python's Pandas library used
for reading data from a Comma-Separated Values (CSV) file and loading
it into a Pandas DataFrame. It's a common method for working with
tabular data stored in CSV format
|
|
| to_sql() |
df.to_sql('table_name', index=False)
|
df.to_sql() is a method in Pandas, a Python data
manipulation library used to write the contents of a DataFrame to a SQL
database. It allows to take data from a DataFrame and store it
structurally within a SQL database table.
|
|
| read_sql() |
df = pd.read_sql(sql_query, conn)
|
read_sql() is a function provided by the Pandas library
in Python for executing SQL queries and retrieving the results into a
DataFrame from an SQL database. It's a convenient way to integrate SQL
database interactions into your data analysis workflows.
|
|
Db2
| Topic | Syntax | Description | Example |
|---|---|---|---|
| connect() |
conn = ibm_db.connect('DATABASE=dbname;
HOST=hostname;PORT=port;UID=username;
PWD=password;', '', '')
|
ibm_db.connect() is a Python function provided by the
ibm_db library, which is used for establishing a connection to an IBM
Db2 or IBM Db2 Warehouse database. It's commonly used in applications
that need to interact with IBM Db2 databases from Python.
|
|
| server_info() |
ibm_db.server_info()
|
ibm_db.server_info(conn) is a Python function provided by the ibm_db library, which is used to retrieve information about the IBM Db2 server to which you are connected.
|
|
| close() |
con.close()
|
con.close() is a method used to close the connection to a
db2 database. When called, it terminates the connection, releasing any
associated resources and ensuring the connection is no longer active.
This is important for managing database connections efficiently and
preventing resource leaks in your db2 database interactions.
|
|
| exec_immediate() |
sql_statement = "SQL statement goes here" |
ibm_db.exec_immediate() is a Python function provided by
the ibm_db library, which is used to execute an SQL statement
immediately without the need to prepare or bind it. It's commonly used
for executing SQL statements that don't require input parameters or
don't need to be prepared in advance.
|
|
Author(s)
SQL Cheat Sheet: Views, Stored Procedures and Transactions
Views
| Topic | Syntax | Description | Example |
|---|---|---|---|
| Create View |
CREATE VIEW view_name AS SELECT column1, column2, ...
FROM table_name
WHERE condition;
|
A CREATE VIEW is an alternative way of
representing data that exists in one or more tables.
|
CREATE VIEW EMPSALARY AS
SELECT EMP_ID, F_NAME, L_NAME,
B_DATE, SEX, SALARY
FROM EMPLOYEES;
|
| Update a View |
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ... FROM table_name
WHERE condition;
|
The CREATE OR REPLACE
VIEW command updates a view.
|
CREATE OR REPLACE VIEW EMPSALARY AS
SELECT EMP_ID, F_NAME, L_NAME, B_DATE, SEX,
JOB_TITLE, MIN_SALARY, MAX_SALARY FROM EMPLOYEES,
JOBS WHERE EMPLOYEES.JOB_ID = JOBS.JOB_IDENT;
|
| Drop a View |
DROP VIEW view_name;
|
Use the DROP VIEW statement
to remove a view from the database.
|
DROP VIEW EMPSALARY;
|
Stored Procedures in IBM Db2 using SQL
| Stored Procedures |
--#SET TERMINATOR @
CREATE PROCEDURE PROCEDURE_NAME
|
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
The default terminator for a stored procedure is semicolon(;). To set a different terminator we use |
--#SET TERMINATOR @
CREATE PROCEDURE RETRIEVE_ALL
|
Stored Procedures in MySQL using phpMyAdmin
| Stored Procedures |
|
A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
The default terminator for a stored procedure is semicolon (;). To set a different terminator we use |
DELIMITER //
|
Transactions with Db2
| Commit command |
|
A COMMIT command is used to persist the changes in the database.
The default terminator for a COMMIT command is semicolon (;). |
CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);
|
| Rollback command |
|
A ROLLBACK command is used to rollback the transactions which are not saved in the database.
The default terminator for a ROLLBACK command is semicolon (;). |
|
Transactions with MySQL
| Commit command |
|
A COMMIT command is used to persist the changes in the database.
The default terminator for a COMMIT command is semicolon (;). |
CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);
|
| Rollback command |
|
A ROLLBACK command is used to rollback the transactions which are not saved in the database.
The default terminator for a ROLLBACK command is semicolon (;). |
|
Db2 Transactions using Stored Procedure
| Commit command |
|
A COMMIT command is used to persist the changes in the database.
The default terminator for a COMMIT command is semicolon (;). |
--#SET TERMINATOR @
CREATE PROCEDURE TRANSACTION_ROSE
LANGUAGE SQL
MODIFIES SQL DATA
|
| Rollback command |
--#SET TERMINATOR @
|
A ROLLBACK command is used to rollback the transactions which are not saved in the database.
The default terminator for a ROLLBACK command is semicolon (;). |
--#SET TERMINATOR @
CREATE PROCEDURE TRANSACTION_ROSE
LANGUAGE SQL
MODIFIES SQL DATA
|
MySQL Transactions using Stored Procedure
| Commit command |
|
A COMMIT command is used to persist the changes in the database.
The default terminator for a COMMIT command is semicolon (;). |
DELIMITER //
|
| Rollback command |
|
A ROLLBACK command is used to rollback the transactions which are not saved in the database.
The default terminator for a ROLLBACK command is semicolon (;). |
DELIMITER //
|
Author(s)
SQL Cheat Sheet: JOIN statements
Joins
| Topic | Syntax | Description | Example |
|---|
| Cross Join |
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
|
The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table.
|
SELECT DEPT_ID_DEP, LOCT_ID
FROM DEPARTMENTS
CROSS JOIN LOCATIONS;
|
| Inner Join |
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
WHERE condition;
|
You can use an inner join in a SELECT statement to retrieve only the rows that satisfy the join conditions on every specified table.
|
select E.F_NAME,E.L_NAME, JH.START_DATE
from EMPLOYEES as E
INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID
where E.DEP_ID ='5';
|
| Left Outer Join |
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
|
The LEFT OUTER JOIN will return all records from the left side table and the matching records from the right table.
|
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME
from EMPLOYEES AS E
LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
|
| Right Outer Join |
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
|
The RIGHT OUTER JOIN returns all records from the right table, and the matching records from the left table.
|
select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME
from EMPLOYEES AS E
RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
|
| Full Outer Join |
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
|
The FULL OUTER JOIN clause results in the inclusion of rows
from two tables. If a value is missing when rows are joined, that value
is null in the result table.
|
select E.F_NAME,E.L_NAME,D.DEP_NAME
from EMPLOYEES AS E
FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;
|
| Self Join |
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
|
A self join is regular join but it can be used to joined with itself.
|
SELECT B.* FROM EMPLOYEES A JOIN EMPLOYEES B
ON A.MANAGER_ID = B.MANAGER_ID
WHERE A.EMP_ID = 'E1001';
|
Joins in MySQL using phpMyAdmin
| Full Outer Join |
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition
|
The UNION operator is used to combine the result-set of two or more SELECT statements.
|
select E.F_NAME,E.L_NAME,D.DEP_NAME
from EMPLOYEES AS E
LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP
|
Author(s)
Saving¶
import os
FromFld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\'
os.chdir(FromFld)
try:
!jupyter nbconvert SQL-HTMLs.ipynb --to html --template pj
except Exception as e:
print('HTML not stored')
import shutil
import os
#file2=Tofld+'P4DSNotes.html'
# The line above copies files from A -> B
#shutil.copy(os.path.join(FromFld,fileh), Tofld)
# The line above copies all the content from A -> B
#shutil.copytree(FromFld, Tofld)
import shutil
FromFld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\Py-Databases-SQL-DS\\IBM\\HTMLS\\'
Tofld='C:\\Users\\Gamaliel\\Documents\\G\\ADD\\IBM_DS\\IBM_DS_Jupyter_Tasks\\Python4DataScience\\'
fileh='SQL-HTMLs.html'
filep='SQL-HTMLs.ipynb'
try:
if os.path.isfile(Tofld+'/'+fileh):
os.remove(Tofld+'/'+fileh)
print(fileh, 'deleted in', Tofld)
shutil.move(os.path.join(FromFld,fileh),os.path.join(Tofld,fileh))
print(fileh, 'replaced in', Tofld)
else:
shutil.move(os.path.join(FromFld,fileh),os.path.join(Tofld,fileh))
print(fileh, 'written in', Tofld)
except Exception as e:
print('HTML not moved')
SQL-HTMLs.html deleted in C:\Users\Gamaliel\Documents\G\ADD\IBM_DS\IBM_DS_Jupyter_Tasks\Python4DataScience\ HTML not moved
